這是最近碰到的問題,要找出那些資料有出現大小寫字母之外的其他文字,字母不包含全形,舉例如下:
資料 | 是否需找出 | 說明 |
---|---|---|
aBc |
X | 只包含字母 |
abC |
O | 包含全形字母 |
ab一 |
O | 包含中文字 |
aBZ |
X | 只包含字母 |
aBZ |
O | 包含全形字母 |
a23 |
O | 包含數字 |
看到這裡有興趣的大大可以先不往下看,實際查詢看看。
產生資料的語法:
DECLARE @Temp TABLE
(
Name NVARCHAR(50)
)
INSERT INTO @Temp
(Name)
VALUES
(N'aBc'),
(N'abC'),
(N'ab一'),
(N'aBZ'),
(N'aBZ'),
(N'a23')
這題的問題點在全形字上,想了很久百思不得其解,最後的結果讓我重新認識了 SQL。
首先看到要查字母想到可以用 LIKE 搭配萬用字元如下。
SELECT *
FROM @Temp
WHERE Name LIKE '%[^A-Za-z]%'
--結果--
Name
----
ab一
a23
不過發現全形字母沒有被找出來,猜想可能是資料庫定序的問題,所以去查了一下資料庫預設的定序是 Chinese_Taiwan_Stroke_CI_AS
,這裡稍微說明一下定序的規則。
定序的規則
_CS
為區分 _CI
為不區分。_AS
為區分 _AI
為不區分。_KS
為區分 _KI
為不區分。_WS
為區分 _WI
為不區分。要查詢定序的詳細資訊可以下這段語法
SELECT name, description FROM fn_helpcollations()
WHERE name LIKE 'Chinese_Taiwan%'
可以看到這個定序是不區分全形和半型的。
因此我想定序加上 WS 應該就可以順利找出全形字了吧,修改後如下。
SELECT *
FROM @Temp
WHERE Name LIKE '%[^A-Za-z]%' COLLATE Chinese_Taiwan_Stroke_CI_AS_WS
--結果--
Name
----
ab一
aBZ
a23
很神奇的全形 Z 被找出來了,可是全形 C 卻沒有,因此我又另外做了一個測試。
SELECT COUNT(*) FROM @Temp Where 'C' LIKE '%[^A-C]%'
COLLATE Chinese_Taiwan_Stroke_CI_AS_WS --等於 6
SELECT COUNT(*) FROM @Temp Where 'C' LIKE '%[^A-D]%'
COLLATE Chinese_Taiwan_Stroke_CI_AS_WS --等於 0
第一段語法 '%[^A-C]%'
如果全形字剛好在區間末端,可以正確判斷出,可第二段語法 '%[^A-D]%'
卻不能,首先想到這是不是 SQL LIKE 的某些限制,可是又不太像,這裡我卡了非常久,就是想不出為什麼。
經過一陣掙扎後,靈光一閃想說將字母排序看看,結果真的讓我看出了端倪。
SELECT * FROM
(
SELECT 'a' AS W --小寫A
UNION ALL SELECT 'A' --大寫A
UNION ALL SELECT 'A' --全形A
UNION ALL SELECT 'C' --全形C
UNION ALL SELECT 'c' --小寫C
UNION ALL SELECT 'C' --大寫C
UNION ALL SELECT 'd' --小寫D
UNION ALL SELECT 'D' --大寫D
UNION ALL SELECT 'D' --全形D
) AS T
ORDER BY W COLLATE Chinese_Taiwan_Stroke_CS_AS_WS
--因為不區分大小寫,會看不太出規律,所以將定序改為區分大小寫
--結果--
W
----
a
A
A
c
C
C
d
D
D
可以發現字母的排序並不是像 ASCII 碼,將小寫、大寫、全形各放在不同區域,而是會將同字母放在一起,並按照 小寫
->大寫
->全形
的順序排序,因此我大膽猜想 LIKE 和一般程式語言的正規式不太一樣,字母區間是以定序的排序為依據。
所以才會有上面測試的結果,因為 全形C
排在 大寫 C
之後,剛好不在 A-C 區間,而 全形C
在 大寫D
之前,所以第二個查詢才會沒有被判斷出來。
既然知道問題點,就知道要怎麼解了,定序除了上面四種規則之外還有特殊的 二進位定序
,此定序規則會按照字碼做排序,如果欄位型態是 VARCHAR
會以地區的 ANSI 字碼排序,如果欄位是 NVARCHAR
會以 Unicode 字碼排序,因此利用以字碼排序的特性,就可以將 小寫
、大寫
、全形
三者完全分開了。
結果
SELECT *
FROM @Temp
WHERE Name LIKE '%[^A-Za-z]%' COLLATE Chinese_Taiwan_Stroke_BIN2
--結果--
Name
----
abC
ab一
aBZ
a23
另一個解
SELECT *
FROM @Temp
WHERE Name LIKE '%[^ABCDEFGHIJKLMNOPQRSTUVWXYZ]%'
COLLATE Chinese_Taiwan_Stroke_CI_AS_WS
在思考過程中還有發現另一個解,雖然比較笨但還是可以解決問題,不過總覺得這樣沒有弄清楚問題的根本,渾身不自在的,哈哈哈。
今天就到這裡了,感謝各位大大觀看。
參考文章
Collation and Unicode Support
[SQL Server]談談SQL Server的定序(Collation)
[SQL]中文字排序和過濾的問題處理
[SQL Server] SQL Server儲存Unicode補充字集(Supplementary Character)
哈~這篇知識可以學更多呢~
其實他的小寫->大寫->全形跟網頁的特殊字帶出的方式一樣呢~
你去Google研究a【a】的號碼順序就知道了~
神Q超人在遇到這個問題之前,我也沒有特別注意這些小細節,
所以才會覺得重新認識了 SQL。
fysh711426
說錯他是大寫→小寫→其他組合字
因為是按照 ASCII 的數字排列到上萬~
以前玩聊天室~就是用ASCII來突破封鎖字的限制@@
恩對,如果是二進制定序就會和 ASCII 順序一樣,用字碼排列,而一般的定序就不是單純用字碼,大順序會是這樣:
更詳細的資訊可以到這裡看,定序的排序圖。
Collation chart for 0409.1252.Latin1_General_CS_AS
然後原來 ASCII 可以用來破封鎖字阿,哈哈哈。
哈...我多查了一點資料@@..
參考:
https://docs.microsoft.com/zh-tw/sql/t-sql/functions/patindex-transact-sql?view=sql-server-2017
這方式也是差不多@@..
DECLARE @Temp TABLE
(
Name NVARCHAR(50)
)
INSERT INTO @Temp
(Name)
VALUES
(N'aBc'),
(N'abC'),
(N'ab一'),
(N'aBZ'),
(N'aBZ'),
(N'a23')
select *
from @Temp
where PATINDEX('%[^A-z]%' COLLATE Chinese_Taiwan_Stroke_BIN2,Name) > 0
感謝大大分享,原來函數裡面可以放定序,PATINDEX 第一次看到,趕快筆記起來。